#! /usr/bin/env python3
# -*- coding:utf-8 -*-

__author__ = 'lilu'

import json
import pymysql

from twisted.enterprise import adbapi

#json
class MyPipeline(object):
    def __init__(self):
        # 打开文件
        self.file = open('data.json', 'w', encoding='utf-8')

    # 该方法用于处理数据
    def process_item(self, item, spider):
        # 读取item中的数据
        line = json.dumps(dict(item), ensure_ascii=False) + "\n"
        # 写入文件
        self.file.write(line)
        # 返回item
        return item

    # 该方法在spider被开启时被调用。
    def open_spider(self, spider):
        pass

    # 该方法在spider被关闭时被调用。
    def close_spider(self, spider):
        pass


#同步
class MySQLPipeline(object):
    def __init__(self):
        self.conn = pymysql.connect(host="192.168.29.131",user="root",password="root",db="test", charset='utf8')
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):
        insert_sql = '''
                    insert into blog(title)
                    values(%s)
                '''
        self.cursor.execute(insert_sql, (
            item['title']
        ))
        self.conn.commit()


    def close_spider(self,spider): #TypeError: close_spider() takes 1 positional argument but 2 were given
        self.cursor.close()
        self.conn.close()



#异步存入数据库目的就是爬虫速度太快数据存入数据库代码，此处异步存储加快速度分布存入，防止数
#据太多数据库堵着
class MysqlTwistedPipline(object):
    #builtins.TypeError: __init__() missing 1 required positional argument: 'db_pool'
    #出现如上错误在from_settinngs 前添加@classmethod
    def __init__(self,dbpool):
        self.dbpool=dbpool
    #从settings获得 数据库配置信息
    @classmethod
    #@classmethod优先级高于__init__使dbpool输出便于初始化
    def from_settings(cls, ):
        # 用一个db_params接收连接数据库的参数
        dbparms=dict(host=settings["MYSQL_HOST"],db=settings["MYSQL_DBNAME"],
                     user=settings["MYSQL_USER"],password=settings["MYSQL_PASSWORD"],
                     charset="utf8",
                    #设置游标类型
                     cursorclass=pymysql.cursors.DictCursor,
                     use_unicode=True,
                     cp_reconnect=True,
                     )
        # 创建连接池
        dbpool=adbapi.ConnectionPool('pymysql',**dbparms)
        # 返回一个pipeline对象
        return cls(dbpool)


    def process_item(self,item,spider):
        #把要执行的sql语句放入连接池
        # 使用Twisted将mysql插入变成异步执行
        # runInteraction可以将传入的函数变成异步的
        query=self.dbpool.runInteraction(self.insert_into,item)
        query.addErrback(self.handle_error,item,spider)
        # 如果sql执行发送错误,自动回调addErrBack()函数
        return item
    def insert_into(self,cursor,item):
        # 会从dbpool取出cursor
        # 执行具体的插入
        # novel_id 动态改变
        insert_sql = ''' insert into lr_novel_section(novel_id, state, create_time, section_title, sort)
                          values('8', '1', %s, %s, %s)
                    '''
        cursor.execute(insert_sql, (
            item['currentTimeT'], item['title'], item['sort']
        ))

        rsId = cursor.lastrowid

        insert_sql1 = '''insert into lr_novel_content(section_id, content, create_time)
                                  values(%s,  %s, %s)
                            '''
        cursor.execute(insert_sql1, (
            rsId, item['content'], item['currentTimeT']
        ))

        # 拿传进的cursor进行执行，并且自动完成commit操作

    def handle_error(self, failure, item, spider):
        # 处理异步插入的异常
        print(failure)
